package com.ls.fw.office.poi.excel.handler;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URL;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.ddf.EscherClientAnchorRecord;
import org.apache.poi.ddf.EscherRecord;
import org.apache.poi.hssf.record.EscherAggregate;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ls.fw.commons.core.utils.date.DateTypeUtil;
import com.ls.fw.office.poi.excel.bean.ClientAnchorDetail;
import com.ls.fw.office.poi.excel.bean.ConvertImageUnits;
import com.ls.fw.office.poi.excel.bean.DimensionedImageType;
import com.ls.fw.office.poi.excel.bean.ExcelPictureData;
import com.ls.fw.office.poi.excel.support.mapper.FieldMapper;


/**
 * 
 * @author lisheng
 * @date 2015年10月8日 下午9:59:22
 * @version V1.0
 */
public abstract class ExcelHandlerAdapter implements IExcelHandler {

	  // Four constants that determine how - and indeed whether - the rows
    // and columns an image may overlie should be expanded to accomodate that
    // image.
    // Passing EXPAND_ROW will result in the height of a row being increased
    // to accomodate the image if it is not already larger. The image will
    // be layed across one or more columns.
    // Passing EXPAND_COLUMN will result in the width of the column being
    // increased to accomodate the image if it is not already larger. The image
    // will be layed across one or many rows.
    // Passing EXPAND_ROW_AND_COLUMN will result in the height of the row
    // bing increased along with the width of the column to accomdate the
    // image if either is not already larger.
    // Passing OVERLAY_ROW_AND_COLUMN will result in the image being layed
    // over one or more rows and columns. No row or column will be resized,
    // instead, code will determine how many rows and columns the image should
    // overlie.
    public static final int EXPAND_ROW = 1;
    public static final int EXPAND_COLUMN = 2;
    public static final int EXPAND_ROW_AND_COLUMN = 3;
    public static final int OVERLAY_ROW_AND_COLUMN = 7;
    
    // Modified to support EMU - English Metric Units - used within the OOXML
    // workbooks, this multoplier is used to convert between measurements in
    // millimetres and in EMUs
    public static final int EMU_PER_MM = 36000;
    
    
	protected String path = "";
	protected Workbook workbook = null;
	protected OutputStream out = null;
	protected Sheet sheet = null;
	protected String version = "";
	
	protected CellStyle titleStyle = null;
	protected CellStyle headStyle = null;
	protected CellStyle bodyStyle = null;
	protected boolean read = false;
	
	public ExcelHandlerAdapter(String path){
		this.path = path;
		workbook = this.createWorkbook();
		sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
		this.init_sytle();
	}

	public ExcelHandlerAdapter(String path, boolean read) {
		this.path = path;
		this.read = read;
		workbook = this.createWorkbook();
		sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
		this.init_sytle();
	}
	
	/**
	 * 创建工作簿
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public abstract Workbook createWorkbook();
	
	
	public ExcelHandlerAdapter(InputStream is){
		try {
			read = true;
			this.workbook = this.createWorkbook(is);
			this.sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
			this.init_sytle();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public ExcelHandlerAdapter(OutputStream out){
		read = false;
		this.out = out;
		this.workbook = this.createWorkbook();
		this.sheet = workbook.getSheetAt(0);
		this.init_sytle();
	}
	
	@Override
	public void init_sytle(){
		Font titleFont = workbook.createFont();
		titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		titleFont.setFontName("宋体");
		titleFont.setFontHeightInPoints((short) 20);
		titleStyle = workbook.createCellStyle();
		titleStyle.setFont(titleFont);
		titleStyle.setBorderTop((short) 1);
		titleStyle.setBorderRight((short) 1);
		titleStyle.setBorderBottom((short) 1);
		titleStyle.setBorderLeft((short) 1);
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER);// 水平    
		titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直   
		
		Font headFont = workbook.createFont();
		headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headFont.setFontName("宋体");
		headFont.setFontHeightInPoints((short) 12);
		headStyle = workbook.createCellStyle();
		headStyle.setFont(headFont);
		headStyle.setBorderTop((short)1);
		headStyle.setBorderRight((short)1);
		headStyle.setBorderBottom((short) 1);
		headStyle.setBorderLeft((short)1);
		headStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		
		bodyStyle = workbook.createCellStyle();
		bodyStyle.setAlignment(CellStyle.ALIGN_LEFT);
	}
	
	public HSSFWorkbook createHSSFWorkbook(String path) throws InvalidFormatException, IOException{
		 return (HSSFWorkbook)WorkbookFactory.create(new File(path));
	}
	
	public XSSFWorkbook createXSSFWorkbook(String path) throws InvalidFormatException, IOException{
		 return (XSSFWorkbook)WorkbookFactory.create(new File(path));
	}
	
	public Workbook createWorkbook(InputStream is) throws IOException,InvalidFormatException {
		read = true;
		if (!is.markSupported()) {
			is = new PushbackInputStream(is, 8);
		}
		//HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls 
		if (POIFSFileSystem.hasPOIFSHeader(is)) {
			this.version = "2003";
			return (HSSFWorkbook)WorkbookFactory.create(is);
		}
		//XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
		if (POIXMLDocument.hasOOXMLHeader(is)) {// 操作office2007需要加xbean.jar
			this.version = "2007";
			return (XSSFWorkbook)WorkbookFactory.create(is);
		}
		throw new IllegalArgumentException("你的excel版本目前poi解析不了");
	}
	
	@Override
	public String getVersion() {
		return this.version;
	}
	
	public void setPath(String path) {
		this.path = path;
	}

	public String getPath() {
		return path;
	}

	public void setWorkbook(Workbook workbook) {
		this.workbook = workbook;
	}

	public Workbook getWorkbook() {
		return workbook;
	}

	@Override
	public Object getCallValue(Sheet sheet, int rowIndex, int colIndex) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			return null;
		}
		return getCellFormatValue(row.getCell((short) colIndex));
	}
	
	@Override
	public Object getCallValue(int rowIndex, int colIndex) {
		return getCallValue(sheet,rowIndex,colIndex);
	}

	@Override
	public Object getCallValue(String cellNumber) {
		return this.getCallValue(sheet,cellNumber);
	}
	
	@Override
	public Object getCallValue(Sheet sheet,String cellNumber) {
		CellReference cellRef = new CellReference(cellNumber);
		return this.getCallValue(sheet,cellRef.getRow(),cellRef.getCol());
	}
	
	@Override
	public Object getCellFormatValue(Cell cell) {
		Object cellvalue = "";
		
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
				// 如果当前Cell的Type为NUMERIC
				case Cell.CELL_TYPE_FORMULA:
					 // CELL_TYPE_FORMULA will never occur  
	                FormulaEvaluator evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();  
	                evaluator.evaluateFormulaCell(cell);  
	                cellvalue = evaluator.evaluate(cell).getStringValue();
	                break;  
				case Cell.CELL_TYPE_NUMERIC: {
					try{
						// 判断当前的cell是否为Date
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							// 如果是Date类型则，转化为Date格式
							Date date = cell.getDateCellValue();
							cellvalue = date;
						} else {// 如果是纯数字
							// 取得当前Cell的数值
							cellvalue = String.valueOf(cell.getNumericCellValue());
							 if (null != cellvalue
							    && cellvalue.toString().indexOf(".") != -1
							    && cellvalue.toString().indexOf("E") != -1) {
							     DecimalFormat df = new DecimalFormat("0");
							     cellvalue = df.parse(cellvalue.toString()).toString();
							 }
						}
					}catch (Exception e) {
						e.printStackTrace();
					}
					
					break;
				}
					// 如果当前Cell的Type为STRING
				case Cell.CELL_TYPE_STRING:
					// 取得当前的Cell字符串
					cellvalue = String.valueOf(cell.getRichStringCellValue().getString());
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					// 取得当前的Cell boolean
					cellvalue = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_BLANK:
					// 取得当前的Cell 空
					cellvalue = String.valueOf("");
					break;
				case Cell.CELL_TYPE_ERROR:  
	                 System.out.println(cell.getErrorCellValue());  
	                 break;  
				default:
					cellvalue = String.valueOf(cell.getRichStringCellValue().getString());
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	@Override
	public Sheet getSheet() {
		return this.sheet;
	}

	@Override
	public Sheet switchSheet(int num) {
		if(num>=0){
			return sheet = this.workbook.getSheetAt(num);
		}
		return this.sheet;
	}
	
	@Override
	public List<Object> getRow(int rowIndex,int startColIndex,int endColIndex) {
		return this.getRow(sheet, rowIndex, startColIndex, endColIndex);
	}
	
	
	@Override
	public List<Object> getRow(Sheet sheet,int rowIndex,int startColIndex,int endColIndex) {
		Row row = null;
		row = sheet.getRow(rowIndex);
		int colNum = row.getPhysicalNumberOfCells();
		if(endColIndex>=0){
			colNum = (colNum>(endColIndex+1)) ? (endColIndex+1) : colNum;
		}
		if(startColIndex<0){
			startColIndex = 0;
		}
		List<Object> cell = null;
		cell = new ArrayList<Object>(colNum-startColIndex);
		for (int j = startColIndex; j < colNum; j++) {
			Object data = this.getCellFormatValue(row.getCell((short) j));
			cell.add(data);
		}
		return cell;
	}
	

	public Map<Integer, Object> getRowMap(int rowIndex,int startColIndex,int endColIndex) {
		return this.getRowMap(sheet, rowIndex, startColIndex, endColIndex);
	}

	@Override
	public Map<Integer, Object> getRowMap(Sheet sheet,int rowIndex,int startColIndex,int endColIndex) {
		Row row = null;
		row = sheet.getRow(rowIndex);
		int colNum = row.getPhysicalNumberOfCells();
		if(endColIndex>=0){
			colNum = (colNum>(endColIndex+1)) ? (endColIndex+1) : colNum;
		}
		if(startColIndex < 0){
			startColIndex = 0;
		}
		Map<Integer, Object> map = new HashMap<Integer, Object>(colNum-startColIndex);
		for (int j = startColIndex; j < colNum; j++) {
			Object data = this.getCellFormatValue(row.getCell((short) j));
			map.put(j, data);
		}
		return map;
	}

	@Override
	public Map<Integer, Object> getRowMap(int rowIndex){
		return this.getRowMap(sheet, rowIndex);
	}

	@Override
	public Map<Integer, Object> getRowMap(Sheet sheet,int rowIndex){
		Row row = null;
		row = sheet.getRow(rowIndex);
		int colNum = row.getPhysicalNumberOfCells();
		return this.getRowMap(rowIndex, 0, colNum);
	}
	
	@Override
	public List<Object> getRow(int rowIndex){
		return this.getRow(sheet, rowIndex);
	}
	
	@Override
	public List<Object> getRow(Sheet sheet,int rowIndex){
		Row row = null;
		row = sheet.getRow(rowIndex);
		int colNum = row.getPhysicalNumberOfCells();
		return this.getRow(rowIndex, 0, colNum);
	}

	@Override
	public List<Object> getCol(int colIndex, int startRowIndex, int endRowIndex) {
		return this.getCol(sheet,colIndex, startRowIndex, endRowIndex);
	}
	
	@Override
	public List<Object> getCol(Sheet sheet,int colIndex, int startRowIndex, int endRowIndex) {
		// 得到总行数+1
		int rowNum = sheet.getLastRowNum();
		int lastIndex = rowNum;
		if(endRowIndex>=0){
			endRowIndex = (rowNum>(endRowIndex)) ? (endRowIndex) : rowNum;
		}
		List<Object> cell = null;
		if(lastIndex <= rowNum){
			if(startRowIndex<0){
				startRowIndex = 0;
			}
			cell = new ArrayList<Object>(lastIndex-startRowIndex);
			for (int j = startRowIndex; j <= lastIndex; j++) {
				Row row = null;
				row = sheet.getRow(j);
				Object data = this.getCellFormatValue(row.getCell((short) colIndex));
				cell.add(data);
			}
		}else{
			cell = new ArrayList<Object>(1);
		}
		return cell;
	}

	@Override
	public List<Object> getCol(int colIndex) {
		return this.getCol(sheet, colIndex);
	}
	
	@Override
	public List<Object> getCol(Sheet sheet,int colIndex) {
		int rowNum = sheet.getLastRowNum();
		return this.getCol(colIndex, 0, rowNum);
	}
	
	@Override
	public List<ExcelPictureData> getAllPictures() throws InvalidFormatException, IOException{
        List<ExcelPictureData> list = new ArrayList<ExcelPictureData>();
        //if(this.getVersion().equals("2003")){
        	 HSSFWorkbook workbook = ((HSSFWorkbook)this.getWorkbook());
        	 List<HSSFPictureData> pictureList = ((HSSFWorkbook)this.getWorkbook()).getAllPictures();
             List<ClientAnchorInfo> clientAnchorRecords = this.getClientAnchorRecords(workbook);
             if (pictureList.size() != clientAnchorRecords.size()) {
                 throw new RuntimeException("解析文件中的图片信息出错，找到的图片数量和图片位置信息数量不匹配");
             }
             int size = pictureList.size();
             for (int i = 0; i < size; i++) {
                 HSSFPictureData pictureData = pictureList.get(i);
                 ClientAnchorInfo anchor = clientAnchorRecords.get(i);
                 HSSFSheet sheet = anchor.sheet;
                 EscherClientAnchorRecord clientAnchorRecord = anchor.clientAnchorRecord;
                 list.add(new ExcelPictureData(workbook, sheet, pictureData, clientAnchorRecord));
             }
        /*}else if(this.getVersion().equals("2007")){
        	 throw new RuntimeException("暂不支持该格式！");
        }*/
        return list ;
    }
 
    private class ClientAnchorInfo {
        public HSSFSheet sheet;
        public EscherClientAnchorRecord clientAnchorRecord;
         
        public ClientAnchorInfo(HSSFSheet sheet, EscherClientAnchorRecord clientAnchorRecord) {
            super();
            this.sheet = sheet;
            this.clientAnchorRecord = clientAnchorRecord;
        }
    }
    
    private List<ClientAnchorInfo> getClientAnchorRecords(HSSFWorkbook workbook) {
        List<ClientAnchorInfo> list = new ArrayList<ClientAnchorInfo>();
         
        EscherAggregate drawingAggregate = null;
        HSSFSheet sheet = null;
        List<EscherRecord> recordList = null;
        Iterator<EscherRecord> recordIter = null;
        int numSheets = workbook.getNumberOfSheets();
        for(int i = 0; i < numSheets; i++) {
            sheet = workbook.getSheetAt(i);
            drawingAggregate = sheet.getDrawingEscherAggregate();
            if(drawingAggregate != null) {
                recordList = drawingAggregate.getEscherRecords();
                recordIter = recordList.iterator();
                while(recordIter.hasNext()) {
                    this.getClientAnchorRecords(sheet, recordIter.next(), 1, list);
                }
            }
        }
        return list;
    }
 
    private void getClientAnchorRecords(HSSFSheet sheet, EscherRecord escherRecord, int level, List<ClientAnchorInfo> list) {
        List<EscherRecord> recordList = null;
        Iterator<EscherRecord> recordIter = null;
        EscherRecord childRecord = null;
        recordList = escherRecord.getChildRecords();
        recordIter = recordList.iterator();
        while(recordIter.hasNext()) {
            childRecord = recordIter.next();
            if(childRecord instanceof EscherClientAnchorRecord) {
                ClientAnchorInfo e = new ClientAnchorInfo(sheet, (EscherClientAnchorRecord) childRecord);
                list.add(e);
            }
            if(childRecord.getChildRecords().size() > 0) {
                getClientAnchorRecords(sheet, childRecord, level+1, list);
            }
        }
    }
    
    @Override
    public Sheet createSheet(String name){
    	return this.sheet = this.getWorkbook().createSheet(name);
    }
	
    @Override
	public void save() throws IOException{
		OutputStream fos = out;
		try {
			if(this.out==null){
				fos = new FileOutputStream(this.getPath());
			}
			this.getWorkbook().write(fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}finally{
			if(fos!=null){
				try {
					fos.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	 
    @Override
    public void addImage(String cellNumber, Sheet sheet, Drawing drawing,
    		String imageFile, double reqImageWidthMM, double reqImageHeightMM,
            DimensionedImageType resizeBehaviour) throws IOException, IllegalArgumentException {
        // Convert the String into column and row indices then chain the
        // call to the overridden addImageToSheet() method.
        CellReference cellRef = new CellReference(cellNumber);
        this.addImageToSheet(cellRef.getCol(), cellRef.getRow(), sheet, drawing,
        		new File(imageFile).toURI().toURL(), reqImageWidthMM, reqImageHeightMM,resizeBehaviour.getValue());
    }
    
    @Override
    public void addImage(String cellNumber,
    		String imageFile, double reqImageWidthMM, double reqImageHeightMM,
            DimensionedImageType resizeBehaviour) throws IOException, IllegalArgumentException {
    	 CellReference cellRef = new CellReference(cellNumber);
         this.addImage(cellRef.getCol(), cellRef.getRow(), sheet, sheet.createDrawingPatriarch(),
        		 imageFile, reqImageWidthMM, reqImageHeightMM,resizeBehaviour);
    }
    
    @Override
    public void addImage(int colNumber, int rowNumber, Sheet sheet, Drawing drawing,
    		String imageFile, double reqImageWidthMM, double reqImageHeightMM,
            DimensionedImageType resizeBehaviour) throws IOException,IllegalArgumentException {
    	this.addImageToSheet(colNumber, rowNumber, sheet, drawing, new File(imageFile).toURI().toURL(), reqImageWidthMM, reqImageHeightMM, resizeBehaviour.getValue());
    }
    
    
    @Override
    public void addImage(int colNumber, int rowNumber,
            String imageFile, double reqImageWidthMM, double reqImageHeightMM,
            DimensionedImageType resizeBehaviour) throws IOException,IllegalArgumentException {
    	this.addImage(colNumber, rowNumber, sheet, sheet.createDrawingPatriarch(), imageFile, reqImageWidthMM, reqImageHeightMM, resizeBehaviour);
    }
    
    private void addImageToSheet(int colNumber, int rowNumber, Sheet sheet, Drawing drawing,
            URL imageFile, double reqImageWidthMM, double reqImageHeightMM,
            int resizeBehaviour) throws IOException,IllegalArgumentException {
       
    	ClientAnchor anchor = null;
        ClientAnchorDetail rowClientAnchorDetail = null;
        ClientAnchorDetail colClientAnchorDetail = null;
        int imageType = 0;

        // Validate the resizeBehaviour parameter.
        if((resizeBehaviour != DimensionedImageType.EXPAND_COLUMN.getValue()) &&
           (resizeBehaviour != DimensionedImageType.EXPAND_ROW.getValue()) &&
           (resizeBehaviour != DimensionedImageType.EXPAND_ROW_AND_COLUMN.getValue()) &&
           (resizeBehaviour != DimensionedImageType.OVERLAY_ROW_AND_COLUMN.getValue())) {
            throw new IllegalArgumentException("Invalid value passed to the " +
                    "resizeBehaviour parameter of AddDimensionedImage.addImageToSheet()");
        }

        // Call methods to calculate how the image and sheet should be
        // manipulated to accomodate the image; columns and then rows.
        colClientAnchorDetail = this.fitImageToColumns(sheet, colNumber,
                reqImageWidthMM, resizeBehaviour);
        rowClientAnchorDetail = this.fitImageToRows(sheet, rowNumber,
                reqImageHeightMM, resizeBehaviour);

        // Having determined if and how to resize the rows, columns and/or the
        // image, create the ClientAnchor object to position the image on
        // the worksheet. Note how the two ClientAnchorDetail records are
        // interrogated to recover the row/column co-ordinates and any insets.
        // The first two parameters are not used currently but could be if the
        // need arose to extend the functionality of this code by adding the
        // ability to specify that a clear 'border' be placed around the image.
        anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor();

        anchor.setDx1(0);
        anchor.setDy1(0);
        anchor.setDx2(colClientAnchorDetail.getInset());
        anchor.setDy2(rowClientAnchorDetail.getInset());
        anchor.setCol1(colClientAnchorDetail.getFromIndex());
        anchor.setRow1(rowClientAnchorDetail.getFromIndex());
        anchor.setCol2(colClientAnchorDetail.getToIndex());
        anchor.setRow2(rowClientAnchorDetail.getToIndex());

        // For now, set the anchor type to do not move or resize the
        // image as the size of the row/column is adjusted. This could easilly
        // become another parameter passed to the method. Please read the note
        // above regarding the behaviour of image resizing.
        anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);

        // Now, add the picture to the workbook. Note that unlike the similar
        // method in the HSSF Examples section, the image type is checked. First,
        // the image files location is identified by interrogating the URL passed
        // to the method, the images type is identified before it is added to the
        // sheet.
        String sURL = imageFile.toString().toLowerCase();
		if( sURL.endsWith(".png") ) {
	            imageType = Workbook.PICTURE_TYPE_PNG;
		}else if( sURL.endsWith("jpg") || sURL.endsWith(".jpeg") ) {
	            imageType = Workbook.PICTURE_TYPE_JPEG;
		}else if( sURL.endsWith("dib")) {
	            imageType = Workbook.PICTURE_TYPE_DIB;
		}else if( sURL.endsWith("emf")) {
	            imageType = Workbook.PICTURE_TYPE_EMF;
		}else if( sURL.endsWith("pict")) {
	            imageType = Workbook.PICTURE_TYPE_PICT;
		}else if( sURL.endsWith("wmf")) {
	            imageType = Workbook.PICTURE_TYPE_WMF;
		}
		else  {
	            throw new IllegalArgumentException("Invalid Image file : " +
	                sURL);
		}
        int index = sheet.getWorkbook().addPicture(
            IOUtils.toByteArray(imageFile.openStream()), imageType);
        drawing.createPicture(anchor, index);
    }
	

    /**
     * Determines whether the sheets columns should be re-sized to accomodate
     * the image, adjusts the columns width if necessary and creates then
     * returns a ClientAnchorDetail object that facilitates construction of
     * an ClientAnchor that will fix the image on the sheet and establish
     * it's size.
     *
     * @param sheet A reference to the sheet that will 'contain' the image.
     * @param colNumber A primtive int that contains the index number of a
     *                  column on the sheet.
     * @param reqImageWidthMM A primitive double that contains the required
     *                        width of the image in millimetres
     * @param resizeBehaviour A primitive int whose value will indicate how the
     *                        width of the column should be adjusted if the
     *                        required width of the image is greater than the
     *                        width of the column.
     * @return An instance of the ClientAnchorDetail class that will contain
     *         the index number of the column containing the cell whose top
     *         left hand corner also defines the top left hand corner of the
     *         image, the index number column containing the cell whose top
     *         left hand corner also defines the bottom right hand corner of
     *         the image and an inset that determines how far the right hand
     *         edge of the image can protrude into the next column - expressed
     *         as a specific number of coordinate positions.
     */
    private ClientAnchorDetail fitImageToColumns(Sheet sheet, int colNumber,
            double reqImageWidthMM, int resizeBehaviour) {

        double colWidthMM = 0.0D;
        double colCoordinatesPerMM = 0.0D;
        int pictureWidthCoordinates = 0;
        ClientAnchorDetail colClientAnchorDetail = null;

        // Get the colum's width in millimetres
        colWidthMM = ConvertImageUnits.widthUnits2Millimetres(
                (short)sheet.getColumnWidth(colNumber));

        // Check that the column's width will accomodate the image at the
        // required dimension. If the width of the column is LESS than the
        // required width of the image, decide how the application should
        // respond - resize the column or overlay the image across one or more
        // columns.
        if(colWidthMM < reqImageWidthMM) {

            // Should the column's width simply be expanded?
            if((resizeBehaviour == EXPAND_COLUMN) ||
               (resizeBehaviour == EXPAND_ROW_AND_COLUMN)) {
                // Set the width of the column by converting the required image
                // width from millimetres into Excel's column width units.
                sheet.setColumnWidth(colNumber,
                        ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM));
                // To make the image occupy the full width of the column, convert
                // the required width of the image into co-ordinates. This value
                // will become the inset for the ClientAnchorDetail class that
                // is then instantiated.
                if(sheet instanceof HSSFSheet) {
                    colWidthMM = reqImageWidthMM;
                    colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS /
                        colWidthMM;
                    pictureWidthCoordinates = (int)(reqImageWidthMM * colCoordinatesPerMM);

                }
                else {
                    pictureWidthCoordinates = (int)reqImageWidthMM * EMU_PER_MM;
                }
                colClientAnchorDetail = new ClientAnchorDetail(colNumber,
                        colNumber, pictureWidthCoordinates);
            }
            // If the user has chosen to overlay both rows and columns or just
            // to expand ONLY the size of the rows, then calculate how to lay
            // the image out across one or more columns.
            else if ((resizeBehaviour == OVERLAY_ROW_AND_COLUMN) ||
                     (resizeBehaviour == EXPAND_ROW)) {
                colClientAnchorDetail = this.calculateColumnLocation(sheet,
                        colNumber, reqImageWidthMM);
            }
        }
        // If the column is wider than the image.
        else {
            if(sheet instanceof HSSFSheet) {
                // Mow many co-ordinate positions are there per millimetre?
                colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS /
                    colWidthMM;
                // Given the width of the image, what should be it's co-ordinate?
                pictureWidthCoordinates = (int)(reqImageWidthMM * colCoordinatesPerMM);
            }
            else {
                pictureWidthCoordinates = (int)reqImageWidthMM *
                        EMU_PER_MM;
            }
            colClientAnchorDetail = new ClientAnchorDetail(colNumber,
                    colNumber, pictureWidthCoordinates);
        }
        return(colClientAnchorDetail);
    }

    /**
     * Determines whether the sheets row should be re-sized to accomodate
     * the image, adjusts the rows height if necessary and creates then
     * returns a ClientAnchorDetail object that facilitates construction of
     * a ClientAnchor that will fix the image on the sheet and establish
     * it's size.
     *
     * @param sheet A reference to the sheet that will 'contain' the image.
     * @param rowNumber A primitive int that contains the index number of a
     *                  row on the sheet.
     * @param reqImageHeightMM A primitive double that contains the required
     *                         height of the image in millimetres
     * @param resizeBehaviour A primitive int whose value will indicate how the
     *                        height of the row should be adjusted if the
     *                        required height of the image is greater than the
     *                        height of the row.
     * @return An instance of the ClientAnchorDetail class that will contain
     *         the index number of the row containing the cell whose top
     *         left hand corner also defines the top left hand corner of the
     *         image, the index number of the row containing the cell whose
     *         top left hand corner also defines the bottom right hand
     *         corner of the image and an inset that determines how far the
     *         bottom edge of the image can protrude into the next (lower)
     *         row - expressed as a specific number of coordinate positions.
     */
    private ClientAnchorDetail fitImageToRows(Sheet sheet, int rowNumber,
            double reqImageHeightMM, int resizeBehaviour) {
        Row row = null;
        double rowHeightMM = 0.0D;
        double rowCoordinatesPerMM = 0.0D;
        int pictureHeightCoordinates = 0;
        ClientAnchorDetail rowClientAnchorDetail = null;

        // Get the row and it's height
        row = sheet.getRow(rowNumber);
        if(row == null) {
            // Create row if it does not exist.
            row = sheet.createRow(rowNumber);
        }

        // Get the row's height in millimetres
        rowHeightMM = row.getHeightInPoints() / ConvertImageUnits.POINTS_PER_MILLIMETRE;

        // Check that the row's height will accomodate the image at the required
        // dimensions. If the height of the row is LESS than the required height
        // of the image, decide how the application should respond - resize the
        // row or overlay the image across a series of rows.
        if(rowHeightMM < reqImageHeightMM) {
            if((resizeBehaviour == EXPAND_ROW) ||
               (resizeBehaviour == EXPAND_ROW_AND_COLUMN)) {
                row.setHeightInPoints((float)(reqImageHeightMM *
                        ConvertImageUnits.POINTS_PER_MILLIMETRE));
                if(sheet instanceof HSSFSheet) {                    
                    rowHeightMM = reqImageHeightMM;
                    rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS /
                        rowHeightMM;
                    pictureHeightCoordinates = (int)(reqImageHeightMM *
                            rowCoordinatesPerMM);
                }
                else {
                    pictureHeightCoordinates = (int)(reqImageHeightMM *
                            EMU_PER_MM);
                }
                rowClientAnchorDetail = new ClientAnchorDetail(rowNumber,
                        rowNumber, pictureHeightCoordinates);
            }
            // If the user has chosen to overlay both rows and columns or just
            // to expand ONLY the size of the columns, then calculate how to lay
            // the image out ver one or more rows.
            else if((resizeBehaviour == OVERLAY_ROW_AND_COLUMN) ||
                    (resizeBehaviour == EXPAND_COLUMN)) {
                rowClientAnchorDetail = this.calculateRowLocation(sheet,
                        rowNumber, reqImageHeightMM);
            }
        }
        // Else, if the image is smaller than the space available
        else {
            if(sheet instanceof HSSFSheet) {
                rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS /
                    rowHeightMM;
                pictureHeightCoordinates = (int)(reqImageHeightMM * rowCoordinatesPerMM);
            }
            else {
                pictureHeightCoordinates = (int)(reqImageHeightMM *
                        EMU_PER_MM);
            }
            rowClientAnchorDetail = new ClientAnchorDetail(rowNumber,
                        rowNumber, pictureHeightCoordinates);
        }
        return(rowClientAnchorDetail);
    }

 
    /**
     * If the image is to overlie more than one column, calculations need to be
     * performed to determine how many columns and whether the image will
     * overlie just a part of one column in order to be presented at the
     * required size.
     *
     * @param sheet The sheet that will 'contain' the image.
     * @param startingColumn A primitive int whose value is the index of the
     *                       column that contains the cell whose top left hand
     *                       corner should be aligned with the top left hand
     *                       corner of the image.
     * @param reqImageWidthMM A primitive double whose value will indicate the
     *                        required width of the image in millimetres.
     * @return An instance of the ClientAnchorDetail class that will contain
     *         the index number of the column containing the cell whose top
     *         left hand corner also defines the top left hand corner of the
     *         image, the index number column containing the cell whose top
     *         left hand corner also defines the bottom right hand corner of
     *         the image and an inset that determines how far the right hand
     *         edge of the image can protrude into the next column - expressed
     *         as a specific number of coordinate positions.
     */
    private ClientAnchorDetail calculateColumnLocation(Sheet sheet,
                                                       int startingColumn,
                                                       double reqImageWidthMM) {
        ClientAnchorDetail anchorDetail = null;
        double totalWidthMM = 0.0D;
        double colWidthMM = 0.0D;
        double overlapMM = 0.0D;
        double coordinatePositionsPerMM = 0.0D;
        int toColumn = startingColumn;
        int inset = 0;

        // Calculate how many columns the image will have to
        // span in order to be presented at the required size.
        while(totalWidthMM < reqImageWidthMM) {
            colWidthMM = ConvertImageUnits.widthUnits2Millimetres(
                    (short)(sheet.getColumnWidth(toColumn)));
            // Note use of the cell border width constant. Testing with an image
            // declared to fit exactly into one column demonstrated that it's
            // width was greater than the width of the column the POI returned.
            // Further, this difference was a constant value that I am assuming
            // related to the cell's borders. Either way, that difference needs
            // to be allowed for in this calculation.
            totalWidthMM += (colWidthMM + ConvertImageUnits.CELL_BORDER_WIDTH_MILLIMETRES);
            toColumn++;
        }
        // De-crement by one the last column value.
        toColumn--;
        // Highly unlikely that this will be true but, if the width of a series
        // of columns is exactly equal to the required width of the image, then
        // simply build a ClientAnchorDetail object with an inset equal to the
        // total number of co-ordinate positions available in a column, a
        // from column co-ordinate (top left hand corner) equal to the value
        // of the startingColumn parameter and a to column co-ordinate equal
        // to the toColumn variable.
        //
        // Convert both values to ints to perform the test.
        if((int)totalWidthMM == (int)reqImageWidthMM) {
            // A problem could occur if the image is sized to fit into one or
            // more columns. If that occurs, the value in the toColumn variable
            // will be in error. To overcome this, there are two options, to
            // ibcrement the toColumn variable's value by one or to pass the
            // total number of co-ordinate positions to the third paramater
            // of the ClientAnchorDetail constructor. For no sepcific reason,
            // the latter option is used below.
            if(sheet instanceof HSSFSheet) {
                anchorDetail = new ClientAnchorDetail(startingColumn,
                    toColumn, ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS);
            }
            else {
                anchorDetail = new ClientAnchorDetail(startingColumn,
                    toColumn, (int)reqImageWidthMM * EMU_PER_MM);
            }
        }
        // In this case, the image will overlap part of another column and it is
        // necessary to calculate just how much - this will become the inset
        // for the ClientAnchorDetail object.
        else {
            // Firstly, claculate how much of the image should overlap into
            // the next column.
            overlapMM = reqImageWidthMM - (totalWidthMM - colWidthMM);

            // When the required size is very close indded to the column size,
            // the calcaulation above can produce a negative value. To prevent
            // problems occuring in later caculations, this is simply removed
            // be setting the overlapMM value to zero.
            if(overlapMM < 0) {
                overlapMM = 0.0D;
            }

            if(sheet instanceof HSSFSheet) {
                // Next, from the columns width, calculate how many co-ordinate
                // positons there are per millimetre
                coordinatePositionsPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS /
                    colWidthMM;
                // From this figure, determine how many co-ordinat positions to
                // inset the left hand or bottom edge of the image.
                inset = (int)(coordinatePositionsPerMM * overlapMM);
            }
            else {
                inset = (int)overlapMM * EMU_PER_MM;
            }

            // Now create the ClientAnchorDetail object, setting the from and to
            // columns and the inset.
            anchorDetail = new ClientAnchorDetail(startingColumn, toColumn, inset);
        }
        return(anchorDetail);
    }

    /**
     * If the image is to overlie more than one rows, calculations need to be
     * performed to determine how many rows and whether the image will
     * overlie just a part of one row in order to be presented at the
     * required size.
     *
     * @param sheet The sheet that will 'contain' the image.
     * @param startingRow A primitive int whose value is the index of the row
     *                    that contains the cell whose top left hand corner
     *                    should be aligned with the top left hand corner of
     *                    the image.
     * @param reqImageHeightMM A primitive double whose value will indicate the
     *                         required height of the image in millimetres.
     * @return An instance of the ClientAnchorDetail class that will contain
     *         the index number of the row containing the cell whose top
     *         left hand corner also defines the top left hand corner of the
     *         image, the index number of the row containing the cell whose top
     *         left hand corner also defines the bottom right hand corner of
     *         the image and an inset that determines how far the bottom edge
     *         can protrude into the next (lower) row - expressed as a specific
     *         number of co-ordinate positions.
     */
    private ClientAnchorDetail calculateRowLocation(Sheet sheet,
            int startingRow, double reqImageHeightMM) {
        ClientAnchorDetail clientAnchorDetail = null;
        Row row = null;
        double rowHeightMM = 0.0D;
        double totalRowHeightMM = 0.0D;
        double overlapMM = 0.0D;
        double rowCoordinatesPerMM = 0.0D;
        int toRow = startingRow;
        int inset = 0;

        // Step through the rows in the sheet and accumulate a total of their
        // heights.
        while(totalRowHeightMM < reqImageHeightMM) {
            row = sheet.getRow(toRow);
            // Note, if the row does not already exist on the sheet then create
            // it here.
            if(row == null) {
                row = sheet.createRow(toRow);
            }
            // Get the row's height in millimetres and add to the running total.
            rowHeightMM = row.getHeightInPoints() /
                    ConvertImageUnits.POINTS_PER_MILLIMETRE;
            totalRowHeightMM += rowHeightMM;
            toRow++;
        }
        // Owing to the way the loop above works, the rowNumber will have been
        // incremented one row too far. Undo that here.
        toRow--;
        // Check to see whether the image should occupy an exact number of
        // rows. If so, build the ClientAnchorDetail record to point
        // to those rows and with an inset of the total number of co-ordinate
        // position in the row.
        //
        // To overcome problems that can occur with comparing double values for
        // equality, cast both to int(s) to truncate the value; VERY crude and
        // I do not really like it!!
        if((int)totalRowHeightMM == (int)reqImageHeightMM) {
            if(sheet instanceof HSSFSheet) {
                clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow,
                    ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS);
            }
            else {
                clientAnchorDetail = new ClientAnchorDetail(startingRow, toRow,
                    (int)reqImageHeightMM * EMU_PER_MM);
            }
        }
        else {
            // Calculate how far the image will project into the next row. Note
            // that the height of the last row assessed is subtracted from the
            // total height of all rows assessed so far.
            overlapMM = reqImageHeightMM - (totalRowHeightMM - rowHeightMM);

            // To prevent an exception being thrown when the required width of
            // the image is very close indeed to the column size.
            if(overlapMM < 0) {
                overlapMM = 0.0D;
            }

            if(sheet instanceof HSSFSheet) {
                rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS /
                    rowHeightMM;
                inset = (int)(overlapMM * rowCoordinatesPerMM);
            }
            else {
                inset = (int)overlapMM * EMU_PER_MM;
            }
            clientAnchorDetail = new ClientAnchorDetail(startingRow,
                        toRow, inset);
        }
        return(clientAnchorDetail);
    }

    @Override
    public CellStyle getTitleStyle(){
    	return this.titleStyle;
    }
    
    @Override
    public CellStyle getHeadStyle(){
    	return this.headStyle;
    }
    
    @Override
    public CellStyle getBodyStyle(){
    	return this.bodyStyle;
    }

	public void setTitleStyle(CellStyle titleStyle) {
		this.titleStyle = titleStyle;
	}

	public void setHeadStyle(CellStyle headStyle) {
		this.headStyle = headStyle;
	}

	public void setBodyStyle(CellStyle bodyStyle) {
		this.bodyStyle = bodyStyle;
	}
   
	@Override
	public Cell addCell(final Row row, final int colIndex,final CellStyle sytle, final int type, final Object value) {
    	Cell cell = row.createCell(colIndex);
		cell.setCellType(type);
		if(sytle!=null){
			cell.setCellStyle(sytle);
		}
		cell.setCellValue(value != null ? value.toString() : "");
		return cell;
	}
     
	@Override
    public Cell addCell(final Row row, final int colIndex,final int type, final Object value) {
		return this.addCell(row, colIndex,this.getBodyStyle(),type, value);
	}

	@Override
    public Cell addCell(final int rowIndex, final int colIndex,final Object value) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		return this.addCell(row, colIndex, value);
	}
    
	@Override
    public Cell addCell(final int rowIndex, final int colIndex,final int type, final Object value) {
    	Row row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		return this.addCell(row, colIndex, type, value);
	}
    
	@Override
    public Cell addCell(final Row row, final int colIndex, final Object value) {
		int type = Cell.CELL_TYPE_STRING;
		if(value != null){
			if(value instanceof Date){
				type = Cell.CELL_TYPE_NUMERIC ;
			}else if(value instanceof Byte){
				type = Cell.CELL_TYPE_NUMERIC ;
			}else if(value instanceof Short){
				type = Cell.CELL_TYPE_NUMERIC ;
			}else if(value instanceof Integer){
				type = Cell.CELL_TYPE_NUMERIC ;
			}else if(value instanceof Long){
				type = Cell.CELL_TYPE_NUMERIC ;
			}else if(value instanceof Float){
				type = Cell.CELL_TYPE_NUMERIC ;
			}else if(value instanceof Double){
				type = Cell.CELL_TYPE_NUMERIC ;
			}else{
				type = Cell.CELL_TYPE_STRING ;
			}
		}
		return this.addCell(row, colIndex, type, value);
    }
	
	@Override
    public Row addRow(final int rowIndex, final List<Object> list) {
        return this.addRow(rowIndex, 0, list);
    }
    
	@Override
    public Row addRow(final int rowIndex
    		, final int startColIndex, final List<Object> list) {
    	Row row = null;
		row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
    	if(list!=null){
    		int index = startColIndex;
    		for (Object value : list) {
				addCell(row, index++, value);
			}
    	}
    	return row;
    }
    
	@Override
    public Row createHead(final int rowIndex
    		, final int startColIndex, final String ...heads){
		Row row = null;
		Cell cell = null;
		// excel head
		row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		if(heads != null){
			int size = heads.length;
			int index = 0;
			int endIndex = startColIndex + size;
			for (int i = startColIndex; i < endIndex; i++) {
				cell = row.createCell(i);
				cell.setCellType(Cell.CELL_TYPE_STRING);
				if(this.getHeadStyle()!=null){
					cell.setCellStyle(this.getHeadStyle());
				}
				cell.setCellValue(heads[index++]);
			}
		}
		return row;
	} 
    
	@Override
    public Row createTitle(final int rowIndex, final int startColIndex
    		, final int endColIndex, final String title){
		//创建title部分
		Row row = null;
		row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		Cell cell = null;
		for (int i = startColIndex; i <=endColIndex; i++) {
			cell = row.getCell(i);
			if(cell==null){
				 cell = row.createCell(i);
			}
			if(this.getTitleStyle()!=null){
				cell.setCellStyle(this.getTitleStyle());
			}
		}
		//合并 title
		sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, startColIndex, endColIndex));
		//设置title的值
		cell = row.getCell(startColIndex);
		if(cell==null){
			 cell = row.createCell(startColIndex);
		}
		if(this.getTitleStyle()!=null){
			cell.setCellStyle(this.getTitleStyle());
		}
		cell.setCellType(Cell.CELL_TYPE_STRING);
		cell.setCellValue(title);
		return row;
    }
    
	
	 public <T> void export(String title, String[] headers,
	         Collection<T> dataset) {
	      this.export(title, headers, dataset,0,0);
	 }
	 
	 public <T> void export(String title, String[] headers,
	         Collection<T> dataset,int rowIndex, final int startColIndex) {
		 this.export(title, headers, dataset,rowIndex,startColIndex,null);
	 }
	 
	 public <T> void export(String title, String[] headers, Collection<T> dataset,FieldMapper mapper){
		 this.export(title, headers, dataset,0,0,mapper);
	 }
	    
	public <T> void export(String title, String[] headers, Collection<T> dataset
			,int rowIndex, final int startColIndex,FieldMapper mapper){
		if(title!=null){
	    	 this.createTitle(rowIndex++, startColIndex, headers.length-1, title);
	     }
		 this.createHead(rowIndex++, startColIndex, headers);
		 //遍历集合数据，产生数据行
	      Iterator<T> it = dataset.iterator();
	      int index = rowIndex;
	      while (it.hasNext()) {
	    	  T t = (T) it.next();
	    	  //利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
	          Field[] fields = t.getClass().getDeclaredFields();
	          for (short i = 0; i < fields.length; i++) {
	             Field field = fields[i];
	             String fieldName = field.getName();
	             String bffix = "get";
//	             if(field.getType().equals(boolean.class)){
//	            	 bffix = "is";
//	             }
	             String getMethodName = bffix
	                    + fieldName.substring(0, 1).toUpperCase()
	                    + fieldName.substring(1);
	             try {
	                 Class<? extends Object> tCls = t.getClass();
	                 Method getMethod = tCls.getMethod(getMethodName,
	                       new Class[] {});
	                 Object value = getMethod.invoke(t, new Object[] {});
	                 if(mapper!=null){
	                	 value = mapper.mapper(fieldName, value, i);
	                 } 
	                 if (value instanceof Date) {
	                	 value = DateTypeUtil.toString(value);
	                 } 
	                 this.addCell(index, i+startColIndex, 0, value);
	             }catch(Exception e){
	            	 e.printStackTrace();
	             }
	          }
	    	  index++;
	      }
	}


	@Override
	public void save(String filePath) throws IOException {
		OutputStream fos = out;
		try {
			fos = new FileOutputStream(this.getPath());
			this.getWorkbook().write(fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}finally{
			if(fos!=null){
				try {
					fos.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}


	@Override
	public void save(OutputStream os) throws IOException {
		this.getWorkbook().write(os);
	}
	
	
	@Override
	public void scan(int sheetNum,int rowStartIndex,int startColIndex, RowColMapper mapper) {
		scan(sheetNum, rowStartIndex, startColIndex, -1, mapper);
	}
	
	
	@Override
	public void scan(int sheetNum,int rowStartIndex,int startColIndex,int endColIndex, RowColMapper mapper) {
		this.switchSheet(sheetNum);
		int rowNum = sheet.getLastRowNum();
		int startIndex = rowStartIndex;
		int index = 0;
		while(startIndex < rowNum){
			Row row = null;
			row = sheet.getRow(startIndex);
			int colNum = row.getPhysicalNumberOfCells();
			if(endColIndex >= 0){
				colNum = (colNum>(endColIndex+1)) ? (endColIndex+1) : colNum;
			}
			if(startColIndex < 0){
				startColIndex = 0;
			}
			for (int j = startColIndex; j < colNum; j++) {
				Object value = this.getCellFormatValue(row.getCell((short) j));
				mapper.mapper(index, j, value); 
			}
			startIndex++;
			index++;
		}
	}
	

	@Override
	public void scan(int rowStartIndex, int startColIndex, RowColMapper mapper) {
		scan(-1, rowStartIndex, startColIndex, mapper);
	}
}
